{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 金融 证券 图谱搭建"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据前面数据梳理结果，下面按照节点和关系分别进行数据抽取\n",
    "\n",
    "实体(节点)方面\n",
    "1. 省份\n",
    "2. 城市\n",
    "3. 公司(上市公司 \\ 基金管理公司 \\ 基金托管公司)\n",
    "4. 人（上市公司管理层）\n",
    "5. 基金\n",
    "6. 行业\n",
    "\n",
    "关系方面\n",
    "1. 城市 - [IN_PROVINCE] - > 省份 \n",
    "2. 公司(上市公司\\基金管理公司\\ 基金托管公司) - [IN_CITY] - > 城市\n",
    "4. 公司(上市公司) - [HAS_MANGER] - > 人（上市公司管理层）  [6类]\n",
    "5. 公司(上市公司) - [IN_INDUSTRY] - > 行业\n",
    "6. 基金  - [HAS_MANAGEMNET] - > 公司(基金管理公司)\n",
    "6. 基金  - [HAS_CUSTODIAN] - > 公司(基金托管公司)\n",
    "7. 基金  - [IN_PORTFOLIO] ->  公司(上市公司)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:41.950244Z",
     "start_time": "2020-01-28T04:42:41.942240Z"
    }
   },
   "outputs": [],
   "source": [
    "def generate_input_files(data,dicts,types,output_path='../data/output/'):\n",
    "    cols =list(dicts.keys())\n",
    "    temp = data[cols]\n",
    "    temp.drop_duplicates(cols,inplace=True)\n",
    "\n",
    "    print('save header {}..')\n",
    "    columns = ','.join(dicts.values())\n",
    "    with open(output_path+'{}-header.csv'.format(types),'w') as f:\n",
    "        f.write(columns)\n",
    "    \n",
    "    print('save_data ...\\n',output_path+'{}.csv'.format(types))\n",
    "    temp.to_csv(output_path+'{}.csv'.format(types),index=False,header=False)\n",
    "    print(temp.shape)\n",
    "    return temp\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_path = '../data_update/output/'\n",
    "\n",
    "data_path = '../data_update/'\n",
    "\n",
    "DATE = '_20200204'\n",
    "\n",
    "company_lsit_name = f'company_lsit_all{DATE}.csv'\n",
    "company_detail_name =  f'company_detail_all{DATE}.csv'\n",
    "stk_managers_name = f'stk_managers{DATE}.csv'\n",
    "fund_basic_name = f'fund_basic{DATE}.csv'\n",
    "fund_company_name = f'fund_company{DATE}.csv'\n",
    "fund_portfolio_name = f'fund_portfolio{DATE}.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:43.105647Z",
     "start_time": "2020-01-28T04:42:43.100643Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts ={}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "!mkdir ../data_update/output/"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-26T14:58:19.217230Z",
     "start_time": "2020-01-26T14:58:19.208235Z"
    }
   },
   "source": [
    "## 1.公司\n",
    "合并 处理 基金管理人\\ 基金托管人\\ 上市公司\\"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1 上市公司"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:44.265247Z",
     "start_time": "2020-01-28T04:42:44.234210Z"
    }
   },
   "outputs": [],
   "source": [
    "usecols = ['ts_code','symbol','name','industry','fullname','market','exchange','list_status',\n",
    "           'list_date','delist_date']\n",
    "\n",
    "df_company_lsit_all =  pd.read_csv(data_path + company_lsit_name,dtype={\"symbol\":str},usecols=usecols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:44.458220Z",
     "start_time": "2020-01-28T04:42:44.440212Z"
    }
   },
   "outputs": [],
   "source": [
    "df_company_lsit_all['list_date'] = df_company_lsit_all['list_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_company_lsit_all['delist_date'] = df_company_lsit_all['delist_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "\n",
    "df_company_lsit_all['label_listed_company']='LISTED_COMPANY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_company_lsit_all['symbol'] = df_company_lsit_all['ts_code'].apply(lambda x: x.split('.')[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2  基金管理公司"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "fund_company 和 fund_basic 两张数据表中，有下面五个银行、一个券商、一个资管 名称公司简称**没有对齐**，\n",
    "\n",
    "这里我们手工对其下：\n",
    "处理 几个 ['中国民生银行', '中国光大银行', '中国工商银行', '中国建设银行', '中国农业银行'] 带有 \"中国\" 的公司"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:45.122649Z",
     "start_time": "2020-01-28T04:42:45.118648Z"
    }
   },
   "outputs": [],
   "source": [
    "comp_simplify_dict = {\n",
    "    '中国民生银行':'民生银行',\n",
    "    '中国光大银行':'光大银行', \n",
    "    '中国工商银行':'工商银行', \n",
    "    '中国建设银行':'建设银行',\n",
    "    '中国农业银行':'农业银行',\n",
    "    '中银国际证券':'中银证券',\n",
    "    '财通证券资管':'财通资管'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "此外，fund_basic 中基金管理人（management）中有 12 个公司 没有 在 资管数据表 fund_company 中出现。 需要额外补全，其全称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基金管理人补齐全称\n",
    "fund_manage_fullname_dict = {\n",
    "    '东证资管':'东方证券资产管理有限公司',\n",
    "    '中泰资管':'中泰证券（上海）资产管理有限公司',\n",
    "    '华泰证券资管':'华华泰证券资产管理公司',\n",
    "    '华融基金':'华融基金管理有限公司',\n",
    "    '博远基金':'博远基金管理有限公司',\n",
    "    '同泰基金':'同泰基金管理有限公司',\n",
    "    '平安基金':'平安基金管理有限公司',\n",
    "    '惠升基金':'惠升基金管理有限公司',\n",
    "    '朱雀基金':'朱雀基金管理有限公司',\n",
    "    '淳厚基金':'淳厚基金管理有限公司',\n",
    "    '睿远基金':'睿远基金管理有限公司',\n",
    "    '西藏东财基金':'西藏东财基金管理有限公司',\n",
    "    '山西证券':'山西证券股份有限公司',\n",
    "    '恒丰银行':'恒丰银行股份有限公司',\n",
    "    '广发银行':'广发银行股份有限公司'\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.2.1 fund_company  中 基金管理人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:45.596079Z",
     "start_time": "2020-01-28T04:42:45.444078Z"
    }
   },
   "outputs": [],
   "source": [
    "# 基金管理人数据\n",
    "usecols = ['name','shortname','province','city','address','office','website',\n",
    "            'setup_date','end_date','main_business','org_code','credit_code']\n",
    "df_fund_company = pd.read_csv(data_path + fund_company_name,usecols=usecols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:45.808078Z",
     "start_time": "2020-01-28T04:42:45.768093Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_company['setup_date'] = df_fund_company['setup_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_company['end_date'] = df_fund_company['end_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:46.531733Z",
     "start_time": "2020-01-28T04:42:46.453650Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_company = df_fund_company.rename(columns ={'name':'fullname','shortname':'name'}).drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:46.684650Z",
     "start_time": "2020-01-28T04:42:46.667650Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_fund_company['name'] = df_fund_company['name'].apply(lambda x : comp_simplify_dict[x] if x in comp_simplify_dict.keys() else x )"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.2.2 fund_basic 中基金管理人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:57.177267Z",
     "start_time": "2020-01-28T05:27:57.086178Z"
    }
   },
   "outputs": [],
   "source": [
    "# 基金数据 \n",
    "df_fund_basic = pd.read_csv(data_path + fund_basic_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fund_basic['management'] = df_fund_basic['management'].apply(lambda x : comp_simplify_dict[x] if x in comp_simplify_dict.keys() else x )\n",
    "\n",
    "df_fund_basic['custodian'] = df_fund_basic['custodian'].apply(lambda x : comp_simplify_dict[x] if x in comp_simplify_dict.keys() else x )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  df_fund_basic 中 管理人 management\n",
    "node_fund_management = df_fund_basic.loc[df_fund_basic.management.notnull(),['management']]\n",
    "node_fund_management = node_fund_management.rename(columns = {'management':'name'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.2.3 合并数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fund_company = pd.merge(df_fund_company,node_fund_management,on=['name'],how='outer').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fund_company['fullname'] = df_fund_company.apply(lambda x:fund_manage_fullname_dict[x['name']] if pd.isnull(x['fullname']) else x['fullname'],axis=1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:46.203523Z",
     "start_time": "2020-01-28T04:42:46.199519Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_company['label_manager'] = 'FUND_MANAGER'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 基金托管公司"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "托管人中存在下面8个没有在大陆上市的公司。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- '中泰证券': 未上市\n",
    "- '中金公司': HK中金公司(03908)\n",
    "- '包商银行': 未上市\n",
    "- '广发银行': 未上市\n",
    "- '广州农商银行': 广州农商银行 01551.HK\n",
    "- '徽商银行':  徽商银行03698.HK\n",
    "- '恒丰银行': 未上市\n",
    "- '渤海银行': 未上市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:47.906519Z",
     "start_time": "2020-01-28T04:42:47.892520Z"
    }
   },
   "outputs": [],
   "source": [
    "node_fund_custodian = df_fund_basic.loc[df_fund_basic.custodian.notnull(),['custodian']].drop_duplicates()\n",
    "node_fund_custodian = node_fund_custodian.rename(columns = {'custodian':'name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:49.114581Z",
     "start_time": "2020-01-28T04:42:49.109585Z"
    }
   },
   "outputs": [],
   "source": [
    "node_fund_custodian['label_custodian'] = 'FUND_CUSTODIAN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(40, 2)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_fund_custodian.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 合并数据·"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:12.979068Z",
     "start_time": "2020-01-28T04:43:12.854053Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies = pd.merge(df_company_lsit_all,df_fund_company,on=['fullname','name'],how='outer').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:13.517525Z",
     "start_time": "2020-01-28T04:43:13.406450Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies = pd.merge(node_companies,node_fund_custodian,on=['name'],how='outer').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18992    广发银行\n",
       "18993    恒丰银行\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_companies[node_companies.fullname.isnull()].name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-27T12:27:30.821355Z",
     "start_time": "2020-01-27T12:27:30.808359Z"
    }
   },
   "source": [
    " 有**2** 家公司没有拼上,分别是 `恒丰银行`,`广发银行`.\n",
    " 通过网查,将两个公司的全名补上"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:28.916110Z",
     "start_time": "2020-01-28T04:43:28.897113Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies.loc[node_companies.name == '恒丰银行','fullname'] = '恒丰银行股份有限公司'\n",
    "node_companies.loc[node_companies.name == '广发银行','fullname'] = '广发银行股份有限公司'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:35.460694Z",
     "start_time": "2020-01-28T04:43:35.432695Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>fullname</th>\n",
       "      <th>market</th>\n",
       "      <th>exchange</th>\n",
       "      <th>list_status</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>...</th>\n",
       "      <th>address</th>\n",
       "      <th>office</th>\n",
       "      <th>website</th>\n",
       "      <th>setup_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>main_business</th>\n",
       "      <th>org_code</th>\n",
       "      <th>credit_code</th>\n",
       "      <th>label_manager</th>\n",
       "      <th>label_custodian</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ts_code, symbol, name, industry, fullname, market, exchange, list_status, list_date, delist_date, label_listed_company, province, city, address, office, website, setup_date, end_date, main_business, org_code, credit_code, label_manager, label_custodian]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 23 columns]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_companies[node_companies.fullname.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "缺失的资管公司数： 0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('缺失的资管公司数：',len(set(df_fund_basic.management) - set(node_companies.name)))\n",
    "\n",
    "set(df_fund_basic.management) - set(node_companies.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.5  处理标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:47:10.092333Z",
     "start_time": "2020-01-28T04:47:10.080338Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies['label_listed_company'] = node_companies['label_listed_company'].fillna('')\n",
    "node_companies['label_manager'] = node_companies['label_manager'].fillna('')\n",
    "node_companies['label_custodian'] = node_companies['label_custodian'].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:49:55.703223Z",
     "start_time": "2020-01-28T04:49:55.698254Z"
    }
   },
   "outputs": [],
   "source": [
    "def join_label(ls):\n",
    "    return 'COMPANY;'+ ';'.join([c for c in ls  if c !=''])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:50:07.071753Z",
     "start_time": "2020-01-28T04:50:06.340676Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies['label'] = node_companies.apply(lambda x: join_label([x['label_listed_company'],x['label_manager'],x['label_custodian']]),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:50:59.563656Z",
     "start_time": "2020-01-28T04:50:59.551658Z"
    }
   },
   "outputs": [],
   "source": [
    "# 基于 上市公司信息 company_lsit_all\n",
    "all_dicts['node_companies'] = {  'fullname':'company:ID(company)', \n",
    "                            'name':'short_name', # 股票名称\n",
    "                            'symbol':'share_code', # 股票全称\n",
    "                            'market':'market', # 市场类型 （主板/中小板/创业板/科创板）\n",
    "                            'exchange':'exchange', # 交易所代码\n",
    "                            'list_status':'list_status', # 上市状态： L上市 D退市 P暂停上市\n",
    "                            'list_date':'list_date:float', # 上市日期\n",
    "                            'delist_date':'delist_date:float', # 退市日期\n",
    "                            'setup_date':'setup_date',   # 成立日期\n",
    "                            'label':':LABEL'\n",
    "                           }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:01.010095Z",
     "start_time": "2020-01-28T04:51:00.707099Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/node_companies.csv\n",
      "(18994, 10)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_companies'\n",
    "temp = generate_input_files(node_companies,dicts=all_dicts[types] ,types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ts_code</th>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>000004.SZ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>symbol</th>\n",
       "      <td>000001</td>\n",
       "      <td>000002</td>\n",
       "      <td>000004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>平安银行</td>\n",
       "      <td>万科A</td>\n",
       "      <td>国农科技</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>industry</th>\n",
       "      <td>银行</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>生物制药</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fullname</th>\n",
       "      <td>平安银行股份有限公司</td>\n",
       "      <td>万科企业股份有限公司</td>\n",
       "      <td>深圳中国农大科技股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>market</th>\n",
       "      <td>主板</td>\n",
       "      <td>主板</td>\n",
       "      <td>主板</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>exchange</th>\n",
       "      <td>SZSE</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>SZSE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>list_status</th>\n",
       "      <td>L</td>\n",
       "      <td>L</td>\n",
       "      <td>L</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>list_date</th>\n",
       "      <td>1.99104e+07</td>\n",
       "      <td>1.99101e+07</td>\n",
       "      <td>1.99101e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>delist_date</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label_listed_company</th>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>province</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>address</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>office</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>website</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>setup_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_date</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main_business</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>org_code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>credit_code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label_manager</th>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label_custodian</th>\n",
       "      <td>FUND_CUSTODIAN</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>label</th>\n",
       "      <td>COMPANY;LISTED_COMPANY;FUND_CUSTODIAN</td>\n",
       "      <td>COMPANY;LISTED_COMPANY</td>\n",
       "      <td>COMPANY;LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                          0  \\\n",
       "ts_code                                           000001.SZ   \n",
       "symbol                                               000001   \n",
       "name                                                   平安银行   \n",
       "industry                                                 银行   \n",
       "fullname                                         平安银行股份有限公司   \n",
       "market                                                   主板   \n",
       "exchange                                               SZSE   \n",
       "list_status                                               L   \n",
       "list_date                                       1.99104e+07   \n",
       "delist_date                                              -1   \n",
       "label_listed_company                         LISTED_COMPANY   \n",
       "province                                                NaN   \n",
       "city                                                    NaN   \n",
       "address                                                 NaN   \n",
       "office                                                  NaN   \n",
       "website                                                 NaN   \n",
       "setup_date                                              NaN   \n",
       "end_date                                                NaN   \n",
       "main_business                                           NaN   \n",
       "org_code                                                NaN   \n",
       "credit_code                                             NaN   \n",
       "label_manager                                                 \n",
       "label_custodian                              FUND_CUSTODIAN   \n",
       "label                 COMPANY;LISTED_COMPANY;FUND_CUSTODIAN   \n",
       "\n",
       "                                           1                       2  \n",
       "ts_code                            000002.SZ               000004.SZ  \n",
       "symbol                                000002                  000004  \n",
       "name                                     万科A                    国农科技  \n",
       "industry                                全国地产                    生物制药  \n",
       "fullname                          万科企业股份有限公司          深圳中国农大科技股份有限公司  \n",
       "market                                    主板                      主板  \n",
       "exchange                                SZSE                    SZSE  \n",
       "list_status                                L                       L  \n",
       "list_date                        1.99101e+07             1.99101e+07  \n",
       "delist_date                               -1                      -1  \n",
       "label_listed_company          LISTED_COMPANY          LISTED_COMPANY  \n",
       "province                                 NaN                     NaN  \n",
       "city                                     NaN                     NaN  \n",
       "address                                  NaN                     NaN  \n",
       "office                                   NaN                     NaN  \n",
       "website                                  NaN                     NaN  \n",
       "setup_date                               NaN                     NaN  \n",
       "end_date                                 NaN                     NaN  \n",
       "main_business                            NaN                     NaN  \n",
       "org_code                                 NaN                     NaN  \n",
       "credit_code                              NaN                     NaN  \n",
       "label_manager                                                         \n",
       "label_custodian                                                       \n",
       "label                 COMPANY;LISTED_COMPANY  COMPANY;LISTED_COMPANY  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_companies.head(3).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：行业"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:29.717202Z",
     "start_time": "2020-01-28T04:51:29.713197Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_industries'] = {'industry':'industry:ID(industry)', # 所属行业 \n",
    "                                'label':':LABEL'\n",
    "                               }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:35.509913Z",
     "start_time": "2020-01-28T04:51:35.501922Z"
    }
   },
   "outputs": [],
   "source": [
    "df_industry = pd.DataFrame({'industry':[c for c in df_company_lsit_all.industry.unique() if not pd.isnull(c)]})\n",
    "df_industry['label']='INDUSTRY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:40.688382Z",
     "start_time": "2020-01-28T04:51:40.618403Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/node_industries.csv\n",
      "(110, 2)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_industries'\n",
    "temp = generate_input_files(df_industry,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：股票-->行业"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:50.947312Z",
     "start_time": "2020-01-28T04:52:50.942311Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_share_in_industry'] = {'fullname':':START_ID(company)', # 股票代码\n",
    "                                      'industry':':END_ID(industry)', # 所属行业\n",
    "                                      'type':':TYPE'\n",
    "                                      }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:51.524828Z",
     "start_time": "2020-01-28T04:52:51.467831Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_share_in_industry = df_company_lsit_all[['industry','fullname']]\n",
    "\n",
    "rel_share_in_industry = rel_share_in_industry[rel_share_in_industry['fullname'].notnull()&rel_share_in_industry['industry'].notnull()]\n",
    "rel_share_in_industry['type']='IN_INDUSTRY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:51.989859Z",
     "start_time": "2020-01-28T04:52:51.921864Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_share_in_industry.csv\n",
      "(3766, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_share_in_industry'\n",
    "temp = generate_input_files(rel_share_in_industry,dicts=all_dicts[types],types = types, output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:56.040141Z",
     "start_time": "2020-01-28T04:52:56.020139Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>fullname</th>\n",
       "      <th>market</th>\n",
       "      <th>exchange</th>\n",
       "      <th>list_status</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>label_listed_company</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>平安银行股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910403</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>万科企业股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910129</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000004.SZ</td>\n",
       "      <td>000004</td>\n",
       "      <td>国农科技</td>\n",
       "      <td>生物制药</td>\n",
       "      <td>深圳中国农大科技股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910114</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000005.SZ</td>\n",
       "      <td>000005</td>\n",
       "      <td>世纪星源</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>深圳世纪星源股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19901210</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000006.SZ</td>\n",
       "      <td>000006</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>深圳市振业(集团)股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19920427</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  symbol  name industry         fullname market exchange  \\\n",
       "0  000001.SZ  000001  平安银行       银行       平安银行股份有限公司     主板     SZSE   \n",
       "1  000002.SZ  000002   万科A     全国地产       万科企业股份有限公司     主板     SZSE   \n",
       "2  000004.SZ  000004  国农科技     生物制药   深圳中国农大科技股份有限公司     主板     SZSE   \n",
       "3  000005.SZ  000005  世纪星源     环境保护     深圳世纪星源股份有限公司     主板     SZSE   \n",
       "4  000006.SZ  000006  深振业A     区域地产  深圳市振业(集团)股份有限公司     主板     SZSE   \n",
       "\n",
       "  list_status  list_date  delist_date label_listed_company  \n",
       "0           L   19910403           -1       LISTED_COMPANY  \n",
       "1           L   19910129           -1       LISTED_COMPANY  \n",
       "2           L   19910114           -1       LISTED_COMPANY  \n",
       "3           L   19901210           -1       LISTED_COMPANY  \n",
       "4           L   19920427           -1       LISTED_COMPANY  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_company_lsit_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：省份"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-20T05:40:34.648826Z",
     "start_time": "2020-01-20T05:40:34.599823Z"
    }
   },
   "source": [
    "##### 上市公司基本信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:58:58.703501Z",
     "start_time": "2020-01-28T04:58:58.664464Z"
    }
   },
   "outputs": [],
   "source": [
    "df_company_detail = pd.read_csv(data_path + company_detail_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:59:24.507939Z",
     "start_time": "2020-01-28T04:59:24.499946Z"
    }
   },
   "outputs": [],
   "source": [
    "df_company_detail['symbol'] = df_company_detail['ts_code'].apply(lambda x:x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:21.016457Z",
     "start_time": "2020-01-28T04:54:21.011488Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_province'] = {'province':'province:ID(province)', # 所属行业 \n",
    "                                'label':':LABEL'\n",
    "                               }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:21.636638Z",
     "start_time": "2020-01-28T04:54:21.584639Z"
    }
   },
   "outputs": [],
   "source": [
    "node_province1 = pd.DataFrame({'province':[c for c in df_company_detail.province.unique() if not pd.isnull(c)]})\n",
    "node_province2 = pd.DataFrame({'province':[c for c in df_fund_company.province.unique() if not pd.isnull(c)]})\n",
    "node_province = pd.concat([node_province1,node_province2]).drop_duplicates()\n",
    "node_province['label']='PROVINCE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:24.191310Z",
     "start_time": "2020-01-28T04:54:24.173306Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/node_province.csv\n",
      "(32, 2)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_province'\n",
    "temp = generate_input_files(node_province,dicts=all_dicts[types],types = types, output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：城市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:29.535893Z",
     "start_time": "2020-01-28T04:54:29.531896Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_city'] = {'city':'city:ID(city)', # 所属行业 \n",
    "                          'label':':LABEL'\n",
    "                          }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:30.306941Z",
     "start_time": "2020-01-28T04:54:30.250944Z"
    }
   },
   "outputs": [],
   "source": [
    "node_city1 = pd.DataFrame({'city':[c for c in df_company_detail.city.unique() if not pd.isnull(c)]})\n",
    "node_city2 = pd.DataFrame({'city':[c for c in df_fund_company.city.unique() if not pd.isnull(c)]})\n",
    "node_city = pd.concat([node_city1,node_city2]).drop_duplicates()\n",
    "\n",
    "node_city['label']='CITY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:31.954057Z",
     "start_time": "2020-01-28T04:54:31.937081Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/node_city.csv\n",
      "(341, 2)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_city'\n",
    "temp = generate_input_files(node_city,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：公司-->城市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:12.646736Z",
     "start_time": "2020-01-28T05:03:12.560742Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_company_in_city1 = pd.merge(df_company_detail,df_company_lsit_all[['symbol','fullname']],on='symbol',how='left')[['fullname','city']]\n",
    "rel_company_in_city2 = df_fund_company[['fullname','city']]\n",
    "\n",
    "rel_company_in_city = pd.concat([rel_company_in_city1,rel_company_in_city2]).drop_duplicates()\n",
    "\n",
    "rel_company_in_city['type']='IN_CITY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:13.263279Z",
     "start_time": "2020-01-28T05:03:13.257278Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_company_in_city'] = {'fullname':':START_ID(company)', # 股票代码\n",
    "                                  'city':':END_ID(city)', # 所属城市\n",
    "                                  'type':':TYPE'\n",
    "                                  }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:45.458429Z",
     "start_time": "2020-01-28T05:03:45.447432Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_company_in_city = rel_company_in_city[rel_company_in_city['city'].notnull()&rel_company_in_city['fullname'].notnull()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:59.046597Z",
     "start_time": "2020-01-28T05:03:58.967630Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_company_in_city.csv\n",
      "(18964, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_company_in_city'\n",
    "temp = generate_input_files(rel_company_in_city,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：城市-->省份"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:04:41.238480Z",
     "start_time": "2020-01-28T05:04:41.235479Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_city_in_province'] = {'city':':START_ID(city)', \n",
    "                                  'province':':END_ID(province)', \n",
    "                                  'type':':TYPE'\n",
    "                                  }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:05:42.129526Z",
     "start_time": "2020-01-28T05:05:42.062523Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_city_in_province1= df_company_detail[['province','city']]\n",
    "rel_city_in_province2= df_fund_company[['province','city']]\n",
    "rel_city_in_province = pd.concat([rel_city_in_province1,rel_city_in_province2]).drop_duplicates()\n",
    "\n",
    "rel_city_in_province = rel_city_in_province[rel_city_in_province['province'].notnull()&rel_city_in_province['city'].notnull()]\n",
    "rel_city_in_province['type']='IN_PROVINCE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:05:59.191692Z",
     "start_time": "2020-01-28T05:05:59.135694Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_city_in_province.csv\n",
      "(341, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_city_in_province'\n",
    "\n",
    "temp = generate_input_files(rel_city_in_province,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：人（上市公司董事高管）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对于公司高管，因为缺少人的唯一身份id，为了避免同名的影响，采用 姓名+性别+生日 进行hash 生成唯一标识。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:06:09.957381Z",
     "start_time": "2020-01-28T05:06:09.952379Z"
    }
   },
   "outputs": [],
   "source": [
    "import hashlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:06:10.521532Z",
     "start_time": "2020-01-28T05:06:10.517536Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_md5(string):\n",
    "    \"\"\"Get md5 according to the string\n",
    "    \"\"\"\n",
    "    byte_string = string.encode(\"utf-8\")\n",
    "    md5 = hashlib.md5()\n",
    "    md5.update(byte_string)\n",
    "    result = md5.hexdigest()\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:06:12.766051Z",
     "start_time": "2020-01-28T05:06:11.855692Z"
    }
   },
   "outputs": [],
   "source": [
    "df_stk_managers = pd.read_csv(data_path + stk_managers_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:48.415427Z",
     "start_time": "2020-01-28T05:09:28.001377Z"
    }
   },
   "outputs": [],
   "source": [
    "df_stk_managers['birthday'] = df_stk_managers['birthday'].apply(lambda x: -1 if pd.isnull(x) else int(x))\n",
    "df_stk_managers['gender'] = df_stk_managers['gender'].apply(lambda x: -1 if x not in ['F','M'] else x)\n",
    "df_stk_managers['hash_cust'] = df_stk_managers.apply(lambda x:get_md5('{}-{}-{}'.format(x['name'],x['gender'],x['birthday'])),axis=1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:48.423378Z",
     "start_time": "2020-01-28T05:09:48.418374Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_managers'] = {'hash_cust':'manager_id:ID(manager_id)', \n",
    "                              'name':'name', \n",
    "                              'gender':'gender',\n",
    "                              'edu':'edu',\n",
    "                              'national':'national',\n",
    "                              'birthday':'birthday:long', \n",
    "                              'label':':LABEL'\n",
    "                          }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:48.489374Z",
     "start_time": "2020-01-28T05:09:48.427376Z"
    }
   },
   "outputs": [],
   "source": [
    "types = 'node_managers'\n",
    "\n",
    "df_stk_managers['label'] = 'MANAGER'\n",
    "node_managers = df_stk_managers[list(all_dicts[types].keys())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:50.489376Z",
     "start_time": "2020-01-28T05:09:48.496380Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/node_managers.csv\n",
      "(163613, 7)\n"
     ]
    }
   ],
   "source": [
    "node_managers = node_managers.sort_values(['name','gender','edu','national']).drop_duplicates('hash_cust',keep='first')\n",
    "\n",
    "temp = generate_input_files(node_managers,dicts=all_dicts[types],types = types, output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：公司（上市公司）--> 人(董事高管)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计划将高管关系分为六类"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "|中文| 英文|数量|占比|\n",
    "|--|--|--|--|\n",
    "|董事会成员 |Has_Director|125040|0.278736|\n",
    "|监事|Has_Supervisor|60332|0.134491|\n",
    "|委员会成员 |Has_Committee|41544|0.092609|\n",
    "|高管 | Has_Manager |102271|0.227980|\n",
    "|核心技术人员 |Has_Core_Technical_Staff|20|0.000045|\n",
    "|其他 | Has_Othoer_Manager|119389|0.266139|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:12:43.471818Z",
     "start_time": "2020-01-28T05:12:43.464854Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_listed_company_has_manager'] = {'fullname':':START_ID(company)', \n",
    "                                              'hash_cust':':END_ID(manager_id)', \n",
    "                                              'begin_date':'begin_date:long',\n",
    "                                              'end_date':'end_date:long',\n",
    "                                              'title':'title',\n",
    "                                              'lev':':TYPE'\n",
    "                                              }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:10:57.736481Z",
     "start_time": "2020-01-28T05:10:56.221486Z"
    }
   },
   "outputs": [],
   "source": [
    "df_stk_managers['symbol'] = df_stk_managers['ts_code'].apply(lambda x:x.split('.')[0])\n",
    "\n",
    "df_stk_managers['begin_date'] = df_stk_managers['begin_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_stk_managers['end_date'] = df_stk_managers['end_date'].apply(lambda x: -1 if pd.isnull(x) else int(x))\n",
    "df_stk_managers['ann_date'] = df_stk_managers['ann_date'].apply(lambda x: -1 if pd.isnull(x) else int(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:11:24.940410Z",
     "start_time": "2020-01-28T05:11:24.544326Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_listed_company_has_manager = pd.merge(df_stk_managers,df_company_lsit_all[['symbol','fullname']],on='symbol',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:19:12.146760Z",
     "start_time": "2020-01-28T05:19:12.142766Z"
    }
   },
   "outputs": [],
   "source": [
    "# 部分为空的的人员，为退市的人员\n",
    "# temp = rel_listed_company_has_manager[rel_listed_company_has_manager['fullname'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:16:20.958871Z",
     "start_time": "2020-01-28T05:16:20.820851Z"
    }
   },
   "outputs": [],
   "source": [
    "types = 'rel_listed_company_has_manager'\n",
    "\n",
    "rel_listed_company_has_manager = rel_listed_company_has_manager[rel_listed_company_has_manager['fullname'].notnull()&rel_listed_company_has_manager['hash_cust'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_listed_company_has_manager = rel_listed_company_has_manager.sort_values(['fullname','hash_cust','title','begin_date','end_date'],ascending=False).drop_duplicates(['fullname','hash_cust','title','begin_date'],keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:16:29.483286Z",
     "start_time": "2020-01-28T05:16:25.839286Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_listed_company_has_manager.csv\n",
      "(432253, 6)\n"
     ]
    }
   ],
   "source": [
    "temp = generate_input_files(rel_listed_company_has_manager,dicts=all_dicts[types],types = types,output_path=output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：基金"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:07.546459Z",
     "start_time": "2020-01-28T05:28:07.523463Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_basic['fund'] = df_fund_basic['ts_code'].apply(lambda x: x.split('.')[0])\n",
    "df_fund_basic['market'] = df_fund_basic['ts_code'].apply(lambda x: x.split('.')[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:08.819361Z",
     "start_time": "2020-01-28T05:28:08.735357Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_basic['found_date'] = df_fund_basic['found_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['due_date'] = df_fund_basic['due_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['list_date'] = df_fund_basic['list_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['issue_date'] = df_fund_basic['issue_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['delist_date'] = df_fund_basic['delist_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:09.623336Z",
     "start_time": "2020-01-28T05:28:09.590368Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_basic['issue_amount'] = df_fund_basic['issue_amount'].apply(lambda x:float(x))\n",
    "df_fund_basic['m_fee'] = df_fund_basic['m_fee'].apply(lambda x:float(x))\n",
    "df_fund_basic['c_fee'] = df_fund_basic['c_fee'].apply(lambda x:float(x))\n",
    "df_fund_basic['min_amount'] = df_fund_basic['min_amount'].apply(lambda x:float(x))\n",
    "\n",
    "df_fund_basic['duration_year'] = df_fund_basic['duration_year'].apply(lambda x:float(x))\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:29:49.572812Z",
     "start_time": "2020-01-28T05:29:49.560821Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_funds'] =  {'fund':'fund_code:ID(fund_code)', \n",
    "                            'name':'name', \n",
    "                            'fund_type':'fund_type', # 投资类型\n",
    "                            'invest_type':'invest_type', # 投资风格\n",
    "                            'type':'type',  # 基金类型 \n",
    "                            'benchmark':'benchmark', # 业绩比较基准\n",
    "                            'market':'market', #  场外'OF', 场内 'SH', 'SZ'\n",
    "                            'found_date':'found_date:long',  # 成立日期\n",
    "                            'delist_date':'delist_date:long',  # 退市日期\n",
    "                            'status':'status',  #  存续状态: D摘牌 I发行 L已上市\n",
    "                            'label':':LABEL' }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 数据预处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-25T15:55:55.530724Z",
     "start_time": "2020-01-25T15:55:55.523724Z"
    }
   },
   "source": [
    "对于 这几个时间，有两种处理方式：1.作为节点属性；2.作为关系属性\n",
    "\n",
    "- 'found_date'  # 成立日期\n",
    "- 'due_date'    # 到期日期\n",
    "- 'list_date'  # 上市时间\n",
    "- 'issue_date'  # 发行日期\n",
    "- 'delist_date'   # 退市日期\n",
    "\n",
    "每个基金都有唯一的5个时间.\n",
    "但是 因为 Neo4j 不持支对于边添加索引.所以考虑查询效率,将这些属性放于节点上."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:31.715870Z",
     "start_time": "2020-01-28T05:28:31.701866Z"
    }
   },
   "outputs": [],
   "source": [
    "types = 'node_funds'\n",
    "\n",
    "df_fund_basic['label'] = 'FUND'\n",
    "node_funds = df_fund_basic[list(all_dicts[types].keys())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:35.444781Z",
     "start_time": "2020-01-28T05:28:35.402785Z"
    }
   },
   "outputs": [],
   "source": [
    "node_funds = node_funds.sort_values(['fund','found_date','delist_date'],ascending=False).drop_duplicates('fund',keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:36.845445Z",
     "start_time": "2020-01-28T05:28:36.624444Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/node_funds.csv\n",
      "(11036, 11)\n"
     ]
    }
   ],
   "source": [
    "temp = generate_input_files(node_funds,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：基金-->托管人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = node_companies[['name','fullname']].rename(columns = {'name':'custodian'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_custodian = pd.merge(df_fund_basic,temp, on='custodian',how='left')[['fund','fullname']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_dicts['rel_fund_has_custodian'] = {'fund':':START_ID(fund_code)',   # TS基金代码\n",
    "                                      'fullname':':END_ID(company)',   # 股票代码\n",
    "                                      'type':':TYPE'\n",
    "                                     }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_custodian['type']='HAS_CUSTODIAN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_fund_has_custodian.csv\n",
      "(11100, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_fund_has_custodian'\n",
    "\n",
    "temp = generate_input_files(rel_fund_has_custodian,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fund</th>\n",
       "      <td>159970</td>\n",
       "      <td>159986</td>\n",
       "      <td>501090</td>\n",
       "      <td>515390</td>\n",
       "      <td>159981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fullname</th>\n",
       "      <td>上海浦东发展银行股份有限公司</td>\n",
       "      <td>广发证券股份有限公司</td>\n",
       "      <td>中国民生银行股份有限公司</td>\n",
       "      <td>招商银行股份有限公司</td>\n",
       "      <td>中国农业银行股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       0              1              2              3  \\\n",
       "fund              159970         159986         501090         515390   \n",
       "fullname  上海浦东发展银行股份有限公司     广发证券股份有限公司   中国民生银行股份有限公司     招商银行股份有限公司   \n",
       "type       HAS_CUSTODIAN  HAS_CUSTODIAN  HAS_CUSTODIAN  HAS_CUSTODIAN   \n",
       "\n",
       "                      4  \n",
       "fund             159981  \n",
       "fullname   中国农业银行股份有限公司  \n",
       "type      HAS_CUSTODIAN  "
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_fund_has_custodian.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：基金-->管理人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = node_companies[['name','fullname']].rename(columns = {'name':'management'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_management = pd.merge(df_fund_basic,temp, on='management',how='left')[['fund','fullname']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_dicts['rel_fund_has_management'] = {'fund':':START_ID(fund_code)',   # TS基金代码\n",
    "                                      'fullname':':END_ID(company)',   # 股票代码\n",
    "                                      'type':':TYPE'\n",
    "                                     }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_management['type']='HAS_MANAGEMENT'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_fund_has_management.csv\n",
      "(11112, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_fund_has_management'\n",
    "\n",
    "temp = generate_input_files(rel_fund_has_management,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fund</th>\n",
       "      <td>159970</td>\n",
       "      <td>159986</td>\n",
       "      <td>501090</td>\n",
       "      <td>515390</td>\n",
       "      <td>159981</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fullname</th>\n",
       "      <td>工银瑞信基金管理有限公司</td>\n",
       "      <td>弘毅远方基金管理有限公司</td>\n",
       "      <td>华宝基金管理有限公司</td>\n",
       "      <td>华安基金管理有限公司</td>\n",
       "      <td>建信基金管理有限责任公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       0               1               2               3  \\\n",
       "fund              159970          159986          501090          515390   \n",
       "fullname    工银瑞信基金管理有限公司    弘毅远方基金管理有限公司      华宝基金管理有限公司      华安基金管理有限公司   \n",
       "type      HAS_MANAGEMENT  HAS_MANAGEMENT  HAS_MANAGEMENT  HAS_MANAGEMENT   \n",
       "\n",
       "                       4  \n",
       "fund              159981  \n",
       "fullname    建信基金管理有限责任公司  \n",
       "type      HAS_MANAGEMENT  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_fund_has_management.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-20T05:41:29.472559Z",
     "start_time": "2020-01-20T05:41:25.448556Z"
    }
   },
   "source": [
    "### 关系: 公募基金持仓数据 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:16:59.107159Z",
     "start_time": "2020-01-28T05:16:54.370937Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio = pd.read_csv(data_path + fund_portfolio_name )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:17:11.873687Z",
     "start_time": "2020-01-28T05:17:10.268694Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio['fund'] = df_fund_portfolio['ts_code'].apply(lambda x: x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:17:14.176314Z",
     "start_time": "2020-01-28T05:17:12.509318Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio['symbol'] = df_fund_portfolio['symbol'].apply(lambda x: x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:11.838611Z",
     "start_time": "2020-01-28T05:27:11.833616Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_fund_listed_company_portfolio'] = {'fund':':START_ID(fund_code)',   # TS基金代码\n",
    "                                                  'fullname':':END_ID(company)',   # 股票代码\n",
    "                                                  'ann_date':'ann_date:float',   # 公告日期\n",
    "                                                  'end_date':'end_date:float' ,  # 截止日期\n",
    "                                                  'mkv':'mkv:float' ,  # 持有股票市值(元)\n",
    "                                                  'amount':'amount:float' ,  # 持有股票数量（股）\n",
    "                                                  'stk_mkv_ratio':'stk_mkv_ratio:float',   # 占股票市值比\n",
    "                                                  'stk_float_ratio':'stk_float_ratio:float',   # 占流通股本比例\n",
    "                                                  'type':':TYPE'\n",
    "                                                 }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:17.679041Z",
     "start_time": "2020-01-28T05:27:13.737945Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio['ann_date'] = df_fund_portfolio['ann_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_portfolio['end_date'] = df_fund_portfolio['end_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "\n",
    "df_fund_portfolio['amount'] = df_fund_portfolio['amount'].apply(float)\n",
    "df_fund_portfolio['stk_mkv_ratio'] = df_fund_portfolio['stk_mkv_ratio'].apply(float)\n",
    "df_fund_portfolio['stk_float_ratio'] = df_fund_portfolio['stk_float_ratio'].apply(float)\n",
    "df_fund_portfolio['mkv'] = df_fund_portfolio['mkv'].apply(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:18.702938Z",
     "start_time": "2020-01-28T05:27:17.682967Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_fund_listed_company_portfolio = pd.merge(df_fund_portfolio,df_company_lsit_all[['symbol','fullname']],on='symbol',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:20.473971Z",
     "start_time": "2020-01-28T05:27:18.704939Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_fund_listed_company_portfolio = rel_fund_listed_company_portfolio.sort_values(['fund','fullname','ann_date','end_date'],ascending=False).drop_duplicates(['fund','fullname'],keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:20.540027Z",
     "start_time": "2020-01-28T05:27:20.475939Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_fund_listed_company_portfolio['type']='IN_PORTFOLIO'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:21.282541Z",
     "start_time": "2020-01-28T05:27:21.276544Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(3623603, 9)"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fund_portfolio.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:37.372258Z",
     "start_time": "2020-01-28T05:27:21.970218Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data_update/output/rel_fund_listed_company_portfolio.csv\n",
      "(1462329, 9)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_fund_listed_company_portfolio'\n",
    "\n",
    "temp = generate_input_files(rel_fund_listed_company_portfolio,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
